

********************************************************************************
****                                 2 IFR                                ******
********************************************************************************

********************************************************************************
******** 2.1  change format of robots data (from factor to string)  ************
********************************************************************************


*** DE
use $orig/robots_DE_operational, clear

//convert robots categories to string
decode robots_aggregated , gen(x)
drop robots_aggregated
rename x robots_aggregated

destring(year), replace

save $data/robots_DE_operational, replace


*** EU
use $orig/robots_EU_non_DE_operational, clear
keep robots_aggregated year robots
rename robots robots_eu

//convert robots categories to string
decode robots_aggregated , gen(x)
drop robots_aggregated
rename x robots_aggregated
destring(year), replace

save $data/robots_EU_non_DE_operational, replace


********************************************************************************
****          2.2 Calculate robots per Kreis/Year  for  2digits           ******
********************************************************************************
//now based only on employment values from 1984-1994
use $data/bhp_employment_r_s_y_NACErev2_2d_1994.dta, clear
rename nace2_2d nace2
merge m:1 nace2 using $orig/cw_robots-aggregated_nace2_2digits
/* _merge == 1: industries without robots not matched*/
*keep only relevant categories
drop if _merge != 3
drop _merge

***merge on robots-aggregated categories: DE
merge m:1 robots_aggregated year using $data/robots_DE_operational
/* _merge == 2: 3-digit industries not matched from robo data */
drop if _merge != 3 // 2-digit industries
drop _merge

***merge on robots-aggregated categories: EU non DE
merge m:1 robots_aggregated year using $data/robots_EU_non_DE_operational
/* _merge == 2: 3-digit industries not matched from robo data */
drop if _merge != 3 // 2-digit industries
drop _merge



** calculate share of industry workers in given Kreis
egen employment_s_y = sum(employment_r_s_y), by(year  robots_aggregated)
gen kreis_share_of_sector = employment_r_s_y / employment_s_y
gen robots_r_s_y = kreis_share_of_sector * robots
gen robots_eu_r_s_y = kreis_share_of_sector * robots_eu


** sum up by kreis
collapse (sum) robots_r = robots_r_s_y ///
	(sum) robots_eu_r = robots_eu_r_s_y ///
	(sum) n_establishments, by(year region)
sort region year
count if n_establishments <= 20
** store 2-digits robots per Kreis
save $data/robots_per_kreis_2digits.dta, replace



********************************************************************************
****         2.3 Calculate robots per Kreis/Year  for  3digits            ******
********************************************************************************
use $data/bhp_employment_r_s_y_NACErev2_3d_1994.dta, clear //now based only on employment values from 1994
rename w08_3_gen nace2
merge m:1 nace2 using ${orig}/cw_robots-aggregated_nace2_3digits

*keep only relevant categories
drop if _merge != 3
drop _merge

***merge on robots-aggregated categories: DE
merge m:1 robots_aggregated year using ${data}/robots_DE_operational
drop if _merge != 3 // 2-digit industries
drop _merge

***merge on robots-aggregated categories: EU non DE
merge m:1 robots_aggregated year using ${data}/robots_EU_non_DE_operational
/* _merge == 2: 3-digit industries not matched from robo data */
drop if _merge != 3 // 2-digit industries
drop _merge


** calculate share of industry workers in given Kreis
egen employment_s_y = sum(employment_r_s_y), by(year  robots_aggregated)
gen kreis_share_of_sector = employment_r_s_y / employment_s_y
gen robots_r_s_y = kreis_share_of_sector * robots
gen robots_eu_r_s_y = kreis_share_of_sector * robots_eu

** sum up by kreis
collapse (sum) robots_r = robots_r_s_y ///
	(sum) robots_eu_r = robots_eu_r_s_y ///
	(sum) n_establishments, by(year region)
sort region year
count if n_establishments <= 20

** store 3-digits robots per Kreis
save $data/robots_per_kreis_3digits.dta, replace


********************************************************************************
****         2.4 Combine 2- & 3-digits, calculate per worker (intens)     ******
********************************************************************************

***
use $data/robots_per_kreis_2digits.dta, clear
append using $data/robots_per_kreis_3digits.dta


collapse (sum) robot_count = robots_r ///
	(sum)  robot_eu_count = robots_eu_r ///
	(sum) n_establishments, by(year region)

*** CALC ROBOTS PER 1000 workers
merge m:1 region using ${orig}/employment_DFS.dta
drop _merge
rename emp_DFS emp

gen robot_intens = robot_count / emp * 1000 
gen robot_eu_intens = robot_eu_count / emp * 1000 
sort region year
count if n_establishments <= 20
rename n_establishments n_robots
drop emp

label var robot_count "estimate of the number of robots based on German data"
label var robot_eu_count "estimate of the number of robots based on non-German EU data"
label var robot_intens "robots count, normalized by employees  based on German data (var: 1A emp)"
label var robot_eu_intens "robots count, normalized by employees based on non-German EU data (var: 1A emp)"
label var n_robots "number of establishments underlying the aggregation"


save $data/robots_per_kreis.dta, replace












